package com.nercel.dsj.gksales.shiro.util;

import com.nercel.dsj.gksales.model.RowData;
import com.nercel.dsj.gksales.model.RowDataList;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil {

	public static RowDataList parseExcelFile(File file){
		RowDataList result = new RowDataList();
		try {
			FormulaEvaluator evaluator;
			Workbook workbook = null;
			try {
				workbook = new HSSFWorkbook(new FileInputStream(file));
			}catch (Exception e){
				workbook = new XSSFWorkbook((new FileInputStream(file)));
			}
			Sheet sheet = workbook.getSheetAt(0);
			evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			int rows = sheet.getPhysicalNumberOfRows();
			for (int i = 0; i < rows; i++) {
				RowData rowData = new RowData();
				result.add(rowData);
				Row row = sheet.getRow(i);
				if(row == null){
					continue;
				}
				int cellNum = row.getPhysicalNumberOfCells();
				for (int j = 0; j < cellNum; j++) {
					Cell cell = row.getCell(j);
					if (cell == null) {
						rowData.add("");
						cellNum++;
						continue;
					}
					String value = "";
					switch (cell.getCellTypeEnum()) {
						case NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
								value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
								break;
							} else {
								value = String.valueOf(cell.getNumericCellValue());
								if(value.contains("M") || value.contains("m") || value.contains("E") || value.contains("e")){
									value = new DecimalFormat("0").format(cell.getNumericCellValue());
								}
							}
							break;
						case STRING:
							value = cell.getStringCellValue();
							break;
						case BOOLEAN:
							value = cell.getBooleanCellValue() + "";
							break;
						case FORMULA:
							String formula = cell.getCellFormula().replaceAll(",\\s*?\\)", ")")
									.replaceAll("\\(\\s*?,", "");
							cell.setCellFormula(formula);
							value = getCellValue(evaluator.evaluate(cell));
							break;
						case BLANK:
							value = "";
							break;
						case ERROR:
						default:
							value = "#ERROR#";
							break;
					}
					rowData.add(value);
				}
			}
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		//System.out.println(result.size());
		return result;
	}

	private static String getCellValue(CellValue cell) {
		String cellValue = null;
		switch (cell.getCellTypeEnum()) {
			case STRING:
				cellValue=cell.getStringValue();
				break;
			case NUMERIC:
				//cellValue = String.valueOf(String.format("%.2f", cell.getNumberValue())).replace(".00", "");
				cellValue = String.valueOf(cell.getNumberValue());
				break;
			case FORMULA:
				System.out.print("FORMULA:");
				break;
			default:
				break;
		}
		return cellValue;
	}


	public static void writeExcel(File file, List<List<String>> listData, Integer startIndex){
		XSSFWorkbook workbook = null;
		try {
			workbook = new XSSFWorkbook((new FileInputStream(file)));
//			workbook.removeSheetAt(0);
			XSSFSheet sheet = workbook.getSheetAt(0);
//			XSSFSheet sheet = workbook.createSheet(sheetName);
			XSSFRow startRow = sheet.getRow(startIndex);
			List<XSSFCellStyle> styles = new ArrayList<>();

			for(int i=0; i<listData.size(); i++){
				int rowIndex = startIndex + i;
				List<String> rowData = listData.get(i);
				if(i == 0){
					XSSFCellStyle last = null;
					for(int n=0; n<rowData.size(); n++) {
						if(n < startRow.getPhysicalNumberOfCells()){
							XSSFCellStyle style = startRow.getCell(n).getCellStyle();
							styles.add(style);
							last = style;
						}else {
							styles.add(last);
						}
					}
				}
				XSSFRow row = sheet.createRow(rowIndex);
				for(int n=0; n<rowData.size(); n++){
					Cell cell = row.createCell(n, CellType.STRING);
					cell.setCellValue(rowData.get(n));
					cell.setCellStyle(styles.get(n));
				}
			}
			FileOutputStream outputStream = new FileOutputStream(file);
			workbook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

//	public static void writeExcel(File file, List<List<String>> listData, Integer startIndex){
//		XSSFWorkbook workbook = null;
//		try {
//			workbook = new XSSFWorkbook((new FileInputStream(file)));
//			XSSFSheet sheet = workbook.getSheetAt(0);
//			XSSFRow startRow = sheet.getRow(startIndex);
//			List<XSSFCellStyle> styles = new ArrayList<>();
//
//
//			for(int i=0; i<listData.size(); i++){
//				int rowIndex = startIndex + i;
//				List<String> rowData = listData.get(i);
//				if(i == 0){
//					XSSFCellStyle last = null;
//					for(int n=0; n<rowData.size(); n++) {
//						if(n < startRow.getPhysicalNumberOfCells()){
//							XSSFCellStyle style = startRow.getCell(n).getCellStyle();
//							styles.add(style);
//							last = style;
//						}else {
//							styles.add(last);
//						}
//					}
//				}
//				XSSFRow row = sheet.createRow(rowIndex);
//				for(int n=0; n<rowData.size(); n++){
//					Cell cell = row.createCell(n, CellType.STRING);
//					cell.setCellValue(rowData.get(n));
//					cell.setCellStyle(styles.get(n));
//				}
//			}
//			FileOutputStream outputStream = new FileOutputStream(file);
//			workbook.write(outputStream);
//			outputStream.flush();
//			outputStream.close();
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//	}

	public static Date parseDate(String dateStr){
		if(dateStr == null || "".equals(dateStr)){
			return null;
		}
		try {
			String date = dateStr.replaceAll("[/\\.年月、]","-").replaceAll("日", "");
			return DateUtil.fomatDateWithPattern(date,"yyyy-M-d");
		}catch (Exception e){
			return null;
		}
	}

	/**将Sheet列号变为列名
	 * @param index 列号, 从0开始
	 * @return 0->A; 1->B...26->AA
	 */
	public static String index2ColName(int index) {
		if (index < 0) {
			return null;
		}
		int num = 65;// A的Unicode码
		String colName = "";
		do {
			if (colName.length() > 0) {
				index--;
			}
			int remainder = index % 26;
			colName = ((char) (remainder + num)) + colName;
			index = (int) ((index - remainder) / 26);
		} while (index > 0);
		return colName;
	}

	/**根据表元的列名转换为列号
	 * @param colName 列名, 从A开始
	 * @return A1->0; B1->1...AA1->26
	 */
	public static int colName2Index(String colName) {
		int index = -1;
		int num = 65;// A的Unicode码
		int length = colName.length();
		for (int i = 0; i < length; i++) {
			char c = colName.charAt(i);
			if (Character.isDigit(c)) break;// 确定指定的char值是否为数字
			index = (index + 1) * 26 + (int)c - num;
		}
		return index;
	}




}
